BEGIN;
CREATE TABLE `books_publisher` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `name` varchar(30) NOT NULL,
    `address` varchar(50) NOT NULL,
    `city` varchar(60) NOT NULL,
    `state_province` varchar(30) NOT NULL,
    `country` varchar(50) NOT NULL,
    `website` varchar(200) NOT NULL
);
CREATE TABLE `books_book` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `title` varchar(100) NOT NULL,
    `publisher_id` integer NOT NULL REFERENCES `books_publisher` (`id`),
    `publication_date` date NOT NULL
);
CREATE TABLE `books_author` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `salutation` varchar(10) NOT NULL,
    `first_name` varchar(30) NOT NULL,
    `last_name` varchar(40) NOT NULL,
    `email` varchar(75) NOT NULL,
    `headshot` varchar(100) NOT NULL
);
CREATE TABLE `books_book_authors` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `book_id` integer NOT NULL REFERENCES `books_book` (`id`),
    `author_id` integer NOT NULL REFERENCES `books_author` (`id`),
    UNIQUE (`book_id`, `author_id`)
);
CREATE INDEX `books_book_publisher_id` ON `books_book` (`publisher_id`);
COMMIT;
